﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace OF.Utility.Export
{
    /// <summary>
    /// 由于MyXls不支持列超过255，所以使用NPOI来处理列超过255的情况
    /// </summary>
    public class NPOIExcelFileExporter
    {
        private const int MaxDataTableRowCountLimit = 65535;
        public byte[] ExportXlsToDownload(DataTable dt, string sheetName, List<ColumnData> columnList,
            out string fileName, List<CellMergeRegion> cellMergeRegionList = null)
        {
            if (dt != null && dt.Rows != null
                   && dt.Rows.Count > MaxDataTableRowCountLimit)
            {
                string msg = string.Format(LangHelper.GetText("对不起，导出失败！本次导出所查询出的结果记录条数超过了允许导出的最大记录条数（{0}），请进一步设置查询条件，以缩小所查出的记录的条数！", "Utility.Export.NPOIExcelFileExporter"), MaxDataTableRowCountLimit);
                throw BuildBizException(msg);
            }
            fileName = DateTime.Now.ToString("yyyy-MM-dd_HH_mm_ss_ffff") + ".xlsx";
            if (cellMergeRegionList == null)
            {
                cellMergeRegionList = new List<CellMergeRegion>();
            }
            var file = BuildSheet(dt, sheetName, columnList, cellMergeRegionList);
            return file.ToArray();
        }

        /// <summary>
        /// 组装数据
        /// </summary>
        /// <param name="dt">column content</param>
        /// <param name="sheetName">datasheet name</param>
        /// <param name="columns">column header</param>
        /// <param name="cellMergeRegions">column header merge region</param>
        /// <returns></returns>
        private MemoryStream BuildSheet(DataTable dt, string sheetName, ICollection<ColumnData> columns,
            IEnumerable<CellMergeRegion> cellMergeRegions)
        {
            try
            {
                var workbook = new XSSFWorkbook();
                var ms = new MemoryStream();
                var sheet = workbook.CreateSheet(sheetName);
                var headerRow = sheet.CreateRow(0);
                // handling header.
                var i = 0;
                var columnsCount = columns.Count;
                var headerFont = workbook.CreateFont();
                headerFont.Boldweight = short.MaxValue;
                var headerCellStyle = GetCellStyle(workbook, headerFont, null,
                      FillPattern.NoFill, null,
                      HorizontalAlignment.Center,
                      VerticalAlignment.Bottom);
                foreach (var column in columns)
                {
                    headerRow.CreateCell(i).SetCellValue(column.Title);
                    //add header style
                    headerRow.Cells[i].CellStyle = headerCellStyle;
                    i++;
                }
                //merge header
                foreach (var cellMergeResion in cellMergeRegions)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(cellMergeResion.RowMin - 1, cellMergeResion.RowMax - 1,
                        cellMergeResion.ColMin - 1, cellMergeResion.ColMax - 1));
                }

                // handling value.
                int rowIndex = 1;
                var xssfRichTextString = new XSSFRichTextString();
                var cellstyle = GetCellStyle(workbook, null, null,
                               FillPattern.NoFill, null,
                               HorizontalAlignment.Left,
                               VerticalAlignment.Bottom);
                foreach (DataRow row in dt.Rows)
                {
                    var dataRow = sheet.CreateRow(rowIndex);
                    var columnIndex = 0;

                    foreach (DataColumn column in dt.Columns)
                    {
                        //filter column
                        if (columns.All(a => a.FieldName != column.ColumnName))
                        {
                            continue;
                        }
                        var cell = dataRow.CreateCell(columnIndex);
                        xssfRichTextString.String = row[column].ToString();
                        cell.SetCellValue(xssfRichTextString);
                        //add style
                        if (columnsCount < 30)
                        {
                            cell.CellStyle = cellstyle;
                        }
                        columnIndex++;
                    }
                    rowIndex++;
                }
                workbook.Write(ms);
                return ms;
            }
            catch (Exception ex)
            {
                var msg = string.Format(LangHelper.GetText("导出出错:{0}", "Utility.Export.NPOIExcelFileExporter"), ex.StackTrace);
                throw BuildBizException(msg);
            }

        }

        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="workbook">Excel操作类</param>
        /// <param name="font">单元格字体</param>
        /// <param name="fillForegroundColor">图案的颜色</param>
        /// <param name="fillPattern">图案样式</param>
        /// <param name="fillBackgroundColor">单元格背景</param>
        /// <param name="ha">垂直对齐方式</param>
        /// <param name="va">垂直对齐方式</param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook, IFont font, XSSFColor fillForegroundColor,
            FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
        {
            ICellStyle cellstyle = workbook.CreateCellStyle();
            cellstyle.FillPattern = fillPattern;
            cellstyle.Alignment = ha;
            cellstyle.VerticalAlignment = va;
            if (fillForegroundColor != null)
            {
                cellstyle.FillForegroundColor = fillForegroundColor.Indexed;

            }
            if (fillBackgroundColor != null)
            {
                cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed;
            }
            if (font != null)
            {
                cellstyle.SetFont(font);
            }
            //有边框
            cellstyle.BorderBottom = BorderStyle.Thin;
            cellstyle.BorderLeft = BorderStyle.Thin;
            cellstyle.BorderRight = BorderStyle.Thin;
            cellstyle.BorderTop = BorderStyle.Thin;
            cellstyle.BottomBorderColor = HSSFColor.Black.Index;
            cellstyle.LeftBorderColor = HSSFColor.Black.Index;
            cellstyle.RightBorderColor = HSSFColor.Black.Index;
            cellstyle.TopBorderColor = HSSFColor.Black.Index;
            return cellstyle;
        }

        private Exception BuildBizException(string msg)
        {
            return new BusinessException(msg);
        }
    }
}
